﻿using System;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Transactions;
using System.Web.Configuration;
using DotNetNuke.Framework.Providers;
using System.IO;
using System.Data.Linq;
using System.Collections.Generic;
using HeroldIT.Dnn.Modules.News.SqlProvider;

namespace HeroldIT.Dnn.Modules.News
{
    public class SqlDataProvider : DataProvider
    {
        #region constants

        private const string ProviderType = "data";

        #endregion

        #region static properties

        private static TransactionScope Transaction
        {
            get
            {
                return new TransactionScope(
                    TransactionScopeOption.Required,
                    new TransactionOptions
                    {
                        IsolationLevel = IsolationLevel.ReadCommitted,
                        Timeout = new TimeSpan(0, 0, 10)
                    });
            }
        }

        private static DataLoadOptions CommonDataLoadOptions
        {
            get
            {
                DataLoadOptions options = new DataLoadOptions();
                options.LoadWith<SqlProvider.NewsEntry>(n => n.NewsEntryDetails);
                return options;
            }
        }

        #endregion

        #region static methods

        private static void HandleUpdatedAndDeletedDetails(SqlProvider.NewsEntry origEntry, SqlProvider.NewsEntry newEntry, NewsEntitiesDataContext context)
        {
            foreach (SqlProvider.NewsEntryDetail origDetail in origEntry.NewsEntryDetails)
            {
                SqlProvider.NewsEntryDetail detail = origDetail;
                var newDetail =
                    newEntry.NewsEntryDetails.SingleOrDefault(nd => nd.NewsEntryDetailId.Equals(detail.NewsEntryDetailId));
                if (null == newDetail)
                {
                    context.NewsEntryDetails.DeleteOnSubmit(detail);
                }
                else
                {
                    detail.Fill(newDetail);
                }
            }
        }

        #endregion

        #region fields

        private readonly ProviderConfiguration providerConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType);
        private readonly string connectionString;
        private readonly string providerPath;
        private readonly string objectQualifier;
        private readonly string databaseOwner;

        #endregion

        #region properties

#if DEBUG
        private StringBuilder DebugLogger
        {
            get; set;
        }
#endif

        internal TextWriter Log
        {
            get; set;
        }

        private NewsEntitiesDataContext RawDataContext
        {
            get
            {
                var context = new NewsEntitiesDataContext(this.connectionString /*,
                                                          new NewsEntitiesMappingSource
                                                          {
                                                              DatabaseOwner = this.databaseOwner,
                                                              ObjectQualifier = this.objectQualifier
                                                          } */);
#if DEBUG
                if (null == this.Log && Debugger.IsAttached)
                {
                    this.DebugLogger = new StringBuilder();
                    this.Log = new StringWriter(this.DebugLogger);
                }
#endif
                if (null != this.Log)
                {
                    context.Log = this.Log;
                }
                return context;
            }
        }

        private NewsEntitiesDataContext DataContext
        {
            get
            {
                var context = this.RawDataContext;
                context.LoadOptions = CommonDataLoadOptions;
                return context;
            }
        }

        #endregion

        #region constructors

        public SqlDataProvider()
        {
            // Read the configuration specific information for this provider
            Provider objProvider = (Provider)this.providerConfiguration.Providers[this.providerConfiguration.DefaultProvider];

            // Read the attributes for this provider
            if (!String.IsNullOrEmpty(objProvider.Attributes["connectionStringName"]) && !String.IsNullOrEmpty(WebConfigurationManager.AppSettings[objProvider.Attributes["connectionStringName"]]))
            {
                this.connectionString = WebConfigurationManager.AppSettings[objProvider.Attributes["connectionStringName"]];
            }
            else
            {
                this.connectionString = objProvider.Attributes["connectionString"];
            }

            this.providerPath = objProvider.Attributes["providerPath"];

            this.objectQualifier = objProvider.Attributes["objectQualifier"];
            if (this.objectQualifier != "" & this.objectQualifier.EndsWith("_") == false)
            {
                this.objectQualifier += "_";
            }

            this.databaseOwner = objProvider.Attributes["databaseOwner"];
            if (this.databaseOwner != "" & this.databaseOwner.EndsWith(".") == false)
            {
                this.databaseOwner += ".";
            }

        }

        #endregion

        #region methods

        public override IList<NewsEntry> ExportNewsEntries(int moduleId)
        {
            using (NewsEntitiesDataContext context = this.DataContext)
            {
                context.ObjectTrackingEnabled = false;
                context.LoadOptions = CommonDataLoadOptions;

                return context.NewsEntries.Where(n => n.ModuleId.Equals(moduleId)).Select(n => n.ToBusiness()).ToList();
            }
        }

        public override int StoreNewsEntry(int moduleId, NewsEntry newsEntry)
        {
            newsEntry.ModuleId = moduleId;
            var linqNewsEntry = newsEntry.ToLinq();
            using (TransactionScope ts = Transaction)
            {
                using (NewsEntitiesDataContext context = this.DataContext)
                {
                    if (newsEntry.NewsEntryId <= 0)
                    {
                        context.NewsEntries.InsertOnSubmit(linqNewsEntry);
                        try
                        {
                            context.SubmitChanges(ConflictMode.FailOnFirstConflict);
                            if (context.ChangeConflicts.Count > 0)
                            {
                                throw new InvalidOperationException("Insert failed due to conflicts.");
                            }
                        }
                        catch (SqlException sqle)
                        {
                            throw new InvalidOperationException("Insert failed.", sqle);
                        }
                    }
                    else
                    {
                        var origEntry = context.NewsEntries.SingleOrDefault(n => n.NewsEntryId.Equals(newsEntry.NewsEntryId));
                        if (null == origEntry)
                        {
                            throw new InvalidOperationException("Update failed: Original entity not found.");
                        }

                        // Handle NewsEntryDetails
                        HandleUpdatedAndDeletedDetails(origEntry, linqNewsEntry, context);

                        // update NewsEntry
                        origEntry.Fill(linqNewsEntry);

                        // insert new NewsEntryDetails
                        origEntry.NewsEntryDetails.AddRange(
                            linqNewsEntry.NewsEntryDetails.Where(
                                newNed => !origEntry.NewsEntryDetails.Any(
                                               origNed => newNed.NewsEntryDetailId == origNed.NewsEntryDetailId))
                                .ToList());

                        try
                        {
                            context.SubmitChanges(ConflictMode.FailOnFirstConflict);
                            if (context.ChangeConflicts.Count > 0)
                            {
                                throw new InvalidOperationException("Update failed due to conflicts.");
                            }
                        }
                        catch (SqlException sqle)
                        {
                            throw new InvalidOperationException("Update failed.", sqle);
                        }
                    }
                    ts.Complete();
                }
            }
            return linqNewsEntry.NewsEntryId;
        }

        public override void DeleteNewsEntry(int moduleId, int newsEntryId)
        {
            using (TransactionScope ts = Transaction)
            {
                using (NewsEntitiesDataContext context = this.DataContext)
                {
                    var newsEntry = context.NewsEntries.SingleOrDefault(n => n.ModuleId.Equals(moduleId) && n.NewsEntryId.Equals(newsEntryId));
                    if (null != newsEntry)
                    {
                        context.NewsEntryDetails.DeleteAllOnSubmit(newsEntry.NewsEntryDetails);
                        context.NewsEntries.DeleteOnSubmit(newsEntry);
                        context.SubmitChanges();
                    }
                    ts.Complete();
                }
            }
        }

        public override NewsEntry ReadNewsEntry(int moduleId, int newsEntryId)
        {
            using (NewsEntitiesDataContext context = this.DataContext)
            {
                context.ObjectTrackingEnabled = false;
                return context.NewsEntries.SingleOrDefault(n => n.ModuleId.Equals(moduleId) && n.NewsEntryId.Equals(newsEntryId)).ToBusiness();
            }
        }

        public override NewsEntry ReadNewsEntry(int moduleId, int newsEntryId, string locale, string fallbackLocale)
        {
            using (NewsEntitiesDataContext context = this.RawDataContext)
            {
                context.ObjectTrackingEnabled = false;
                DataLoadOptions options = CommonDataLoadOptions;
                options.AssociateWith<SqlProvider.NewsEntry>(
                    n => n.NewsEntryDetails
                             .OrderByDescending(nd => nd.Locale.Equals(locale))
                             .ThenByDescending(nd => nd.IsDefaultLocale)
                             .ThenByDescending(nd => nd.Locale.Equals(fallbackLocale))
                             .Take(1));
                context.LoadOptions = options;
                return context.NewsEntries.SingleOrDefault(n => n.ModuleId.Equals(moduleId) && n.NewsEntryId.Equals(newsEntryId)).ToBusiness();
            }
        }

        public override PagedList<NewsEntry> FindNewsEntries(int moduleId, ICollection<string> categories, string filterText, DateTime? from, DateTime? until, string locale,
                                                             string fallbackLocale, int skip, int take, bool allEntries)
        {
            using (NewsEntitiesDataContext context = this.RawDataContext)
            {
                context.ObjectTrackingEnabled = false;
                DataLoadOptions options = CommonDataLoadOptions;
                //if (allEntries)
                //{
                //    options.AssociateWith<SqlProvider.NewsEntry>(
                //        n => n.NewsEntryDetails
                //                 .OrderByDescending(
                //                 nd => nd.Locale.Equals(locale))
                //                 .ThenByDescending(
                //                 nd => nd.IsDefaultLocale)
                //                 .ThenByDescending(
                //                 nd => nd.Locale.Equals(fallbackLocale)));
                //}
                //else
                //{
                    options.AssociateWith<SqlProvider.NewsEntry>(
                        n => n.NewsEntryDetails
                                 .OrderByDescending(nd => nd.Locale.Equals(locale))
                                 .ThenByDescending(nd => nd.IsDefaultLocale)
                                 .ThenByDescending(nd => nd.Locale.Equals(fallbackLocale))
                                 .Take(1));
                //}
                context.LoadOptions = options;
                var entries = context.NewsEntries.Where(
                    n =>
                    n.ModuleId.Equals(moduleId) && 
                    n.NewsEntryDetails.Where(
                        d => d.Locale.Equals(locale) || d.IsDefaultLocale || d.Locale.Equals(fallbackLocale)).Count() > 0);
                if (!allEntries)
                {
                    entries = entries.Where(
                        n => !n.IsHidden &&
                             (null == n.PublishOn || n.PublishOn < DateTime.Now) &&
                             (null == n.PublishUntil || n.PublishUntil > DateTime.Now) &&
                             n.NewsEntryDetails.Where(
                                 d => !d.IsHidden).Count() > 0);
                }
                if (null != categories && categories.Count > 0)
                {
                    var predicate = PredicateBuilder.False<SqlProvider.NewsEntry>();
                    foreach (var category in categories)
                    {
                        string cat = category;
                        predicate = predicate.Or(n => (";" + n.Categories + ";").Contains(";" + cat + ";"));
                    }
                    entries = entries.Where(predicate);
                }
                if (null != from)
                {
                    entries = entries.Where(n => n.DisplayDate >= from);
                }
                if (null != until)
                {
                    entries = entries.Where(n => n.DisplayDate < until);
                }
                if (!String.IsNullOrEmpty(filterText))
                {
                    entries =
                        entries.Where(
                            n => n.NewsEntryDetails.Any(
                                     d => d.Headline.Contains(filterText) || d.Subline.Contains(filterText)
                                          || d.Source.Contains(filterText) || d.TeaserText.Contains(filterText)
                                          || d.DetailText.Contains(filterText)));
                }
                int totalCount = entries.Count();
                entries = entries.OrderByDescending(n => n.IsSticky).ThenByDescending(n => n.DisplayDate).ThenByDescending(n => n.DisplayOrder).ThenByDescending(n => n.NewsEntryId);
                var entriesOnPage = entries.Skip(skip).Take(take).Select(n => n.ToBusiness());
                return new PagedList<NewsEntry>(totalCount, entriesOnPage);
            }
        }

        public override IEnumerable<string> ReadTeaserImageNames(int moduleId)
        {
            using (NewsEntitiesDataContext context = this.DataContext)
            {
                context.ObjectTrackingEnabled = false;
                var result = context.NewsEntryDetails
                    .Where(ned => ned.NewsEntry.ModuleId.Equals(moduleId)
                                  && null != ned.TeaserImageUrl
                                  && !ned.TeaserImageUrl.Equals(String.Empty))
                    .Select(ned => ned.TeaserImageUrl);
                return result.ToList();
            }
        }

        public override IEnumerable<string> ReadDetailImageNames(int moduleId)
        {
            using (NewsEntitiesDataContext context = this.DataContext)
            {
                context.ObjectTrackingEnabled = false;
                var result = context.NewsEntryDetails
                    .Where(ned => ned.NewsEntry.ModuleId.Equals(moduleId)
                                  && null != ned.DetailImageUrl
                                  && !ned.DetailImageUrl.Equals(String.Empty))
                    .Select(ned => ned.DetailImageUrl);
                return result.ToList();
            }
        }

        public override IEnumerable<string> ReadCategories(int moduleId)
        {
            using (NewsEntitiesDataContext context = this.DataContext)
            {
                context.ObjectTrackingEnabled = false;
                var result = context.NewsEntries
                    .Where(ne => ne.ModuleId.Equals(moduleId) && null != ne.Categories && ne.Categories.Length > 0)
                    .Select(ne => ne.Categories);
                var categories =
                    result.AsEnumerable().SelectMany(c => c.Split(new[] {';'}, StringSplitOptions.RemoveEmptyEntries))
                        .Distinct().OrderBy(c => c);
                return categories.ToList();
            }
        }

        public override IEnumerable<string> ReadLocales(int moduleId)
        {
            using (NewsEntitiesDataContext context = this.DataContext)
            {
                context.ObjectTrackingEnabled = false;
                var result = context.NewsEntryDetails
                    .Where(ned => ned.NewsEntry.ModuleId.Equals(moduleId))
                    .Select(ned => ned.Locale).Distinct();
                return result.ToList();
            }
        }

        #endregion
    }
}
